package com.weddingee.dao.home;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.weddingee.dao.base.BaseDao;
import com.weddingee.exception.DBException;
import com.weddingee.exception.DaoException;
import com.weddingee.model.News;

public class NewsDao extends BaseDao {
	
	// save
	public News save(News news)throws DaoException {
		Connection conn = null;
		PreparedStatement pstm  = null;
			try {
				conn = getConn();
				String sql = "insert into news values(null,?,?,?,?,?)";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				
				//NAME
				pstm.setString(1,  news.getName());
				//DATE
				pstm.setDate(2, new Date(news.getCreateTime().getTime()));
				//MESSAGE
				pstm.setString(3, news.getMessage());	
				//PICURL
				pstm.setString(4, news.getPicurl());	
				//ABSMESSAGE
				pstm.setString(5, news.getAbsMessage());
				
				int res = pstm.executeUpdate();
				if( res > 0){
					ResultSet rset = pstm.getGeneratedKeys();
					rset.next();
					news.setNewsId(rset.getLong(1));
					return news;
				}
				
				return null;
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error");
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
		
	}

	// list
	public List<News> listNews(int page) {
		Connection conn = null;
		PreparedStatement pstm  = null;
		
		 List<News> newsList = new ArrayList<News>();
			try {
				conn = getConn();
				String sql = "SELECT * FROM news order by id desc LIMIT ?,10";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				pstm.setInt(1,  (page-1)*10);
				ResultSet rset = pstm.executeQuery();
				int i=0;
				while(rset.next())
				{
				News news = new News();
				news.setNewsId(rset.getLong(1));
				news.setName(rset.getString(2));
				news.setCreateTime(rset.getDate(3));
				news.setMessage(rset.getString(4));
				news.setPicurl(rset.getString(5));
				news.setAbsMessage(rset.getString("ABSMESSAGE"));
				newsList.add(i,news);
				i++;
				}
			    return newsList;
				
				
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error",e);
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
		
		
		
	}
    
	//update
	public News update (News news){
		Connection conn = null;
		PreparedStatement pstm  = null;
			try {
				conn = getConn();
				String sql = "update news set NAME=? , DATE=? , MESSAGE=? , PICURL=?,ABSMESSAGE=? where id= ?";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				
				//NAME
				pstm.setString(1,  news.getName());
				//DATE
				pstm.setDate(2, new Date(news.getCreateTime().getTime()));
				//MESSAGE
				pstm.setString(3, news.getMessage());	
				//PICURL
				pstm.setString(4, news.getPicurl());	
				//ABSMESSAGE
				pstm.setString(5, news.getAbsMessage());
				//id
				pstm.setLong(6, news.getNewsId());
				int res = pstm.executeUpdate();
				return news;

			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error");
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
	}

	
	// find
	public News find(Long newsId) {
		Connection conn = null;
		PreparedStatement pstm  = null;
		News news = new News();
			try {
				conn = getConn();
				String sql = "select * from news where id= ?";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				
				//newsId
				pstm.setLong(1, newsId);
				
				
				ResultSet rset = pstm.executeQuery();
				rset.next();
				news.setNewsId(newsId);
				news.setName(rset.getString("NAME"));
				news.setCreateTime(rset.getDate("DATE"));
				news.setMessage(rset.getString("MESSAGE"));
				news.setPicurl(rset.getString("PICURL"));
				news.setAbsMessage(rset.getString("ABSMESSAGE"));
				return news;
				
				
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error",e);
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
		
		
	}

	// delete
	public String delete(Long newsId) {
		Connection conn = null;
		PreparedStatement pstm  = null;
			try {
				conn = getConn();
				String sql = "delete from news where id= ?";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				
				//newsId
				pstm.setLong(1, newsId);
				int res = pstm.executeUpdate();
				return null;
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error");
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
	}

	public int listNewsTotalCount() {
		Connection conn = null;
		PreparedStatement pstm  = null;
		int totalCount = 0;
		List<News> newsList = new ArrayList<News>();
		try {
			conn = getConn();
			String sql = "SELECT count(*) FROM news";
			pstm = conn.prepareStatement(sql);
			ResultSet rset = pstm.executeQuery();

			while(rset.next())
			{
				totalCount = rset.getInt(1);
			}
		    return totalCount;
			
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error",e);
		} finally{
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}
}